﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace AddingProduct
{
    class AddingProduct
    {
        static void Main(string[] args)
        {
            InsertProject("Praskova", null, null, null, null, null, null, null, true);
            InsertProject("Dinq", 3, 7, "Po edna torba", 10.00M, 12, 12, 25, false);
        }


        private static void InsertProject(string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice,
            int? unitsInStock, int? unitsOnOrder, int? reorderLevel, bool discontinued)
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " + "Database=Northwind; " + "Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand("INSERT INTO Products " +
                  "(productName, supplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) VALUES " +
                  "(@productName, @supplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)", dbCon);
                cmd.Parameters.Add(new SqlParameter("@productName", productName));
                SqlParameter sqlParameterSupplier = new SqlParameter("@supplierID", supplierID);
                if (supplierID == null)
                {
                    sqlParameterSupplier.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterSupplier);
                SqlParameter sqlParameterCategoryID = new SqlParameter("@CategoryID", categoryID);
                if (categoryID == null)
                {
                    sqlParameterCategoryID.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterCategoryID);
                SqlParameter sqlParameterQuantityPerUnit = new SqlParameter("@QuantityPerUnit", quantityPerUnit);
                if (quantityPerUnit == null)
                {
                    sqlParameterQuantityPerUnit.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterQuantityPerUnit);
                SqlParameter sqlParameterUnitPrice = new SqlParameter("@UnitPrice", unitPrice);
                if (unitPrice == null)
                {
                    sqlParameterUnitPrice.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterUnitPrice);
                SqlParameter sqlParameterUnitsInStock = new SqlParameter("@UnitsInStock", unitsInStock);
                if (unitsInStock == null)
                {
                    sqlParameterUnitsInStock.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterUnitsInStock);
                SqlParameter sqlParameterUnitsOnOrder = new SqlParameter("@UnitsOnOrder", unitsOnOrder);
                if (unitsOnOrder == null)
                {
                    sqlParameterUnitsOnOrder.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterUnitsOnOrder);
                SqlParameter sqlParameterReorderLevel = new SqlParameter("@ReorderLevel", reorderLevel);
                if (reorderLevel == null)
                {
                    sqlParameterReorderLevel.Value = DBNull.Value;
                }
                cmd.Parameters.Add(sqlParameterReorderLevel);
                SqlParameter sqlParameterDiscontinued;
                if (discontinued)
                {
                    sqlParameterDiscontinued = new SqlParameter("@discontinued", true);
                }
                else
                {
                    sqlParameterDiscontinued = new SqlParameter("@discontinued", false);
                }
                cmd.Parameters.Add(sqlParameterDiscontinued);
                cmd.ExecuteNonQuery();
            }
        }
    }
}
